DROP PROCEDURE Eladas;
DROP PROCEDURE UjTermekHozzadas;
DROP PROCEDURE UjVideokartya;
DROP PROCEDURE UjRAM;
DROP PROCEDURE UjGyarto;

CREATE PROCEDURE Eladas(
	@termekAr INT,
	@termekNev VARCHAR(30),
	@vasarloNev VARCHAR(30),
	@vasarloSzemSzam NCHAR(13),
	@vasarloLakhely VARCHAR(30),
	@vasarloTelSzam NCHAR(10),
	@ar INT,
	@regcom VARCHAR(30),
	@bank VARCHAR(30),
	@bankSzamla VARCHAR(30),
	@codFiscal INT,
	@nrOrd INT)
AS
	SET TRAN ISOLATION LEVEL READ UNCOMMITTED
	BEGIN TRANSACTION

		DECLARE @termekID INT;
		DECLARE @aktualisDatum  DATETIME;
		
		SET @aktualisDatum = (select convert(varchar, getdate(), 101))
		
		
		SET @termekID = (SELECT Termekek.TermekID FROM Termekek WHERE TermekNev = @termekNev)

		IF((SELECT Darabszam FROM Raktar WHERE Raktar.TermekID = @termekID)>0)
		BEGIN
			IF EXISTS(SELECT SzemSzam FROM Vasarlo WHERE @vasarloSzemSzam = SzemSzam ) 
			BEGIN
				UPDATE Raktar SET Darabszam = (SELECT Darabszam-1 FROM Raktar WHERE TermekID = @termekID AND Ar = @termekAr ) WHERE TermekID = @termekID AND Ar = @termekAr AND Darabszam >0 	
				INSERT INTO Vasarlas VALUES (@vasarloSzemSzam,@termekID,@aktualisDatum,@codFiscal,@nrOrd,@ar);	
			END
			ELSE
			BEGIN
				INSERT INTO Vasarlo VALUES (@vasarloSzemSzam,@vasarloLakhely,@vasarloTelSzam,@vasarloNev,@regcom,@bank,@bankSzamla)
				UPDATE Raktar SET Darabszam = (SELECT Darabszam-1 FROM Raktar WHERE TermekID = @termekID AND Ar = @termekAr ) WHERE TermekID = @termekID AND Ar = @termekAr AND Darabszam >0 	
				INSERT INTO Vasarlas VALUES (@vasarloSzemSzam,@termekID,@aktualisDatum,@codFiscal,@nrOrd,@ar);	
			END
		END
		ELSE
		BEGIN
			ROLLBACK TRANSACTION;
		END	
	COMMIT TRANSACTION
GO

/* //TESZT
EXEC Eladas 2100, 'Ipad', 'Szecsi Zsolt', '1911004191124', 'Szekelykeresztur', '0746489324',1200 

SELECT * FROM Vasarlas
SELECT * FROM Vasarlo
*/


CREATE PROCEDURE UjTermekHozzadas(
	@termekNev VARCHAR(30),
	@processzor VARCHAR(30),
	@merevlemez VARCHAR(30),
	@kepernyo VARCHAR(30),
	@videokartya VARCHAR(30),
	@ram VARCHAR(30),
	@garancia INT,
	@darabszzam INT,
	@gyarto VARCHAR(20),
	@kategoria VARCHAR(25),
	@ar INT)
AS
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION
	
	DECLARE @gyartoID INT;
	DECLARE @kategoriaID INT;
	DECLARE @ujTermekID INT;
	
	SET @gyartoID = (SELECT GyartoID FROM Gyartok WHERE GyartoNev = @gyarto)
	SET @kategoriaID = (SELECT KategoriaID FROM Kategoriak WHERE KategoriaNev = @kategoria )
	
	INSERT INTO Termekek VALUES (@termekNev,@gyartoID,@kategoriaID,@garancia)

	SET @ujTermekID = (SELECT TermekID FROM Termekek WHERE TermekNev = @termekNev)
	
	INSERT INTO Raktar VALUES(@ujTermekID,@darabszzam,@ar)
			
	INSERT INTO Felepites VALUES ( @ujTermekID, (SELECT AlkatreszID FROM Processzorok WHERE Nev = @processzor) )
	INSERT INTO Felepites VALUES ( @ujTermekID, (SELECT AlkatreszID FROM Merevlemezek WHERE Nev = @merevlemez) )
	INSERT INTO Felepites VALUES ( @ujTermekID, (SELECT AlkatreszID FROM Kepernyok WHERE Nev = @kepernyo) )
	INSERT INTO Felepites VALUES ( @ujTermekID, (SELECT AlkatreszID FROM Videokartyak WHERE Nev = @videokartya) )
	INSERT INTO Felepites VALUES ( @ujTermekID, (SELECT AlkatreszID FROM RAM WHERE Nev = @ram) )
	
	COMMIT TRANSACTION
GO

/*
/////////////////////////////////////////////
*/

CREATE PROCEDURE UjVideokartya(
	@nev VARCHAR(30),
	@teljesitmeny INT,
	@gyarto VARCHAR(20))
AS
BEGIN
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

		DECLARE @kovID INT;
		DECLARE @tulID INT;
		SET @kovID = (SELECT MAX(AlkID)+1 FROM Alkatreszek );
		INSERT INTO Alkatreszek VALUES (@kovID);
		INSERT INTO Videokartyak VALUES (@kovID,@nev,(SELECT GyartoID FROM Gyartok WHERE GyartoNev = @gyarto));	
		
		IF EXISTS (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'MB' AND Nev = 'Teljsitmeny' AND Ertek = @teljesitmeny )
		BEGIN
			SET @tulID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'MB' AND Nev = 'Teljsitmeny' AND Ertek = @teljesitmeny );
			INSERT INTO AlkTulajd VALUES(@kovID,@tulID);
		END
		ELSE
		BEGIN
			INSERT INTO Tulajdonsagok VALUES ('MB','Teljesitmeny',@teljesitmeny); 
			SET @tulID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'MB' AND Nev = 'Teljesitmeny' AND Ertek = @teljesitmeny );
			INSERT INTO AlkTulajd VALUES(@kovID,@tulID);
		END
	COMMIT TRANSACTION
END
GO

--EXEC UjVideokartya 'mertNemMegy', 23, 'Apple'


/*
////////////////////////////////////////////
*/

CREATE PROCEDURE UjRAM(
	@nev VARCHAR(30),
	@meret INT,
	@tipus INT,
	@gyarto VARCHAR(20) )
AS
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

		DECLARE @kovID INT;
		DECLARE @tulID INT;
		SET @kovID = (SELECT MAX(AlkID)+1 FROM Alkatreszek );
		INSERT INTO Alkatreszek VALUES (@kovID);
		INSERT INTO RAM VALUES (@kovID,@nev,(SELECT GyartoID FROM Gyartok WHERE GyartoNev = @gyarto));	
		
		IF NOT EXISTS (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'MB' AND Nev = 'Meret' AND Ertek = @meret )
		BEGIN
			INSERT INTO Tulajdonsagok VALUES ('MB','Meret',@meret); 
		END	
		SET @tulID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'MB' AND Nev = 'Meret' AND Ertek = @meret );
			INSERT INTO AlkTulajd VALUES(@kovID,@tulID);
		

		IF NOT EXISTS (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'DDR' AND Nev = 'Tipus' AND Ertek = @tipus )
		BEGIN
			INSERT INTO Tulajdonsagok VALUES ('DDR','Tipus',@tipus); 
		END	
		SET @tulID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = 'DDR' AND Nev = 'Tipus' AND Ertek = @tipus );
			INSERT INTO AlkTulajd VALUES(@kovID,@tulID);
	COMMIT TRANSACTION
GO

/*
//////////////////////////////////////////
*/

CREATE PROCEDURE UjGyarto(
	@nev VARCHAR(20),
	@orszag VARCHAR(25))
AS
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	IF NOT EXISTS (SELECT * FROM Gyartok WHERE GyartoNev = @nev )
	BEGIN
		IF EXISTS (SELECT * FROM Orszagok WHERE OrszagNev = @orszag )
		BEGIN
			INSERT INTO Gyartok VALUES(@nev,(SELECT OrszagID FROM Orszagok WHERE OrszagNev = @orszag ) )
		END
		ELSE
		BEGIN
			INSERT INTO Orszagok VALUES( @orszag );
			INSERT INTO Gyartok VALUES(@nev,(SELECT OrszagID FROM Orszagok WHERE OrszagNev = @orszag ) )
		END
	END
	
	COMMIT TRANSACTION
GO


Create Procedure InsertUser(
	@nev varchar(25),
	@priv int,
	@passwd nvarchar(25))
As
	SET TRAN ISOLATION LEVEL READ UNCOMMITTED
	BEGIN TRANSACTION

	declare @hashVegEredmeny varbinary(8000);
	
	IF( EXISTS(SELECT * FROM Userek WHERE UserName = @nev))
	BEGIN
		ROLLBACK TRANSACTION
	END
	ELSE
	BEGIN 
		SET @hashVegEredmeny = HASHBYTES('SHA1', @passwd);
		Insert Into Userek Values(@nev,@priv,@hashVegEredmeny);
	END
	
	COMMIT TRANSACTION
Go


drop function isItGood
Create Function isItGood(
@nev varchar(25),
@passwd nvarchar(25))

returns int
as 
begin
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

		declare @hashVegEredmeny varbinary(8000);
		declare @mit int; 
		SET @hashVegEredmeny = HASHBYTES('SHA1', @passwd);
		
		IF exists (select * from Userek where Username=@nev)
			begin
				if (select UserPasswd from Userek where Username=@nev) <> @hashVegEredmeny
					begin
						set @mit= -1;
					end
				else 
					begin
						set @mit=(select UserPriviledge from Userek where Username=@nev);
					end
			end
	COMMIT TRANSACTION	

	return @mit;
		
end